<?
use adminmatrix\migration\Migrator;
use  adminmatrix\migration\db\Column;

class Views extends Migrator
{
    /**
         * Change Method.
         *
         * Write your reversible migrations using this method.
         *
         * More information on writing migrations is available here:
         * http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
         *
         * The following commands can be used in this method and Phinx will
         * automatically reverse them when rolling back:
         *
         *    createTable
         *    renameTable
         *    addColumn
         *    renameColumn
         *    addIndex
         *    addForeignKey
         *
         * Remember to call "create()" or "update()" and NOT "save()" when working
         * with the Table class.
         */
    public function up()
    {
        $this->createAdditionalViews();
        $this->createadmin_organization_department_position_view();
        $this->createAdditionalViewss();
    }

    public function down(){
        $pref = env('DB_PREFIX');
        $this->execute("
        DROP VIEW {$pref}view_user_organization_department_position ,
        {$pref}view_user_role_permission, {$pref}admin_organization_department_position_view , 
        {$pref}view_user_org_department_position");

    }

    public function createAdditionalViews()
    {
        $preif = env('DB_PREFIX');

        // 1. 查询用户绑定的角色, id, 角色名称，权限id
        if (!$this->hasTable('view_user_role_permission')) {
            $this->execute("
    CREATE VIEW `{$preif}view_user_role_permission` AS
    SELECT 
        u.`id` AS `user_id`,
        r.`id` AS `role_id`,
        r.`name` AS `role_name`,
        GROUP_CONCAT(rp.`permission_id` ORDER BY rp.`permission_id` SEPARATOR ',') AS `permission_ids`
    FROM 
        `{$preif}admin_user` u
    JOIN `{$preif}admin_user_role` ur ON u.`id` = ur.`user_id`
    JOIN `{$preif}admin_role` r ON ur.`role_id` = r.`id`
    LEFT JOIN `{$preif}admin_role_permission` rp ON r.`id` = rp.`role_id`
    GROUP BY 
        u.`id`, r.`id`, r.`name`
                ");
        }

//
        if(!$this->hasTable('view_user_org_department_position'))
        {
            $this->execute("
            CREATE VIEW {$preif}view_user_org_department_position AS
SELECT 
    u.id AS user_id,
    u.username,
    o.name AS organization_name,
    d.name AS department_name,
    p.name AS position_name
FROM 
    {$preif}admin_user u
-- 连接管理员信息表，获取用户的组织、部门、职位ID
LEFT JOIN 
    {$preif}admin_info i ON u.id = i.admin_id
-- 连接组织表，获取组织名称
LEFT JOIN 
    {$preif}admin_organization o ON i.organization_id = o.id
-- 连接部门表，获取部门名称
LEFT JOIN 
    {$preif}admin_department d ON i.department_id = d.id
-- 连接职位表，获取职位名称
LEFT JOIN 
    {$preif}admin_position p ON i.position_id = p.id;    
                ");
        }
    }


    public function createAdditionalViewss()
    {
        $preif = env('DB_PREFIX');


        // 2. 查询用户所在组织、部门、职位
        if (!$this->hasTable('view_user_organization_department_position')) {
            $this->execute("
CREATE VIEW `{$preif}view_user_organization_department_position` AS
SELECT 
    u.`id` AS `user_id`,
    o.`name` AS `organization_name`,
    d.`name` AS `department_name`,
    p.`name` AS `position_name`
FROM 
    `{$preif}admin_user` u
JOIN `{$preif}admin_user_organization` uo ON u.`id` = uo.`user_id`
JOIN `{$preif}admin_position` p ON uo.`position_id` = p.`id`
JOIN `{$preif}admin_department` d ON p.`department_id` = d.`id`
JOIN `{$preif}admin_organization` o ON d.`organization_id` = o.`id`
            ");
        }
    }

    private  function createadmin_organization_department_position_view()
    {
        $preif = env('DB_PREFIX');

        if(!$this->hasTable('admin_organization_department_position_view'))
            $this->execute("
        CREATE VIEW {$preif}admin_organization_department_position_view AS
SELECT 
    mo.id AS organization_id,
    mo.name AS organization_name,
    mo.description AS organization_description,
    md.id AS department_id,
    md.name AS department_name,
    md.description AS department_description,
    mp.id AS position_id,
    mp.name AS position_name,
    mp.description AS position_description
FROM 
    {$preif}admin_organization mo
LEFT JOIN 
    {$preif}admin_department md ON mo.id = md.organization_id
LEFT JOIN 
    {$preif}admin_position mp ON md.id = mp.department_id; 
        ");
    }
}